package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Customer;

public class CustomerDB extends DB {

	private String UPDATE_CUSTOMER = "UPDATE customer SET name=?,address=?,bank=?,account=? WHERE cid=?";
	private String ADD_CUSTOMER = "INSERT INTO customer VALUES (?,?,?,?)";
	private String GET_CUSTOMER_BY_ID = "SELECT * FROM customer WHERE cid=?";
	private String GET_CUSTOMERS = "SELECT * FROM customer";
	private String DEL_CUSTOMER = "DELETE FROM customer WHERE cid=?";
	private String GET_CUSTOMER_NEW = "SELECT TOP 1 * FROM customer ORDER BY cid DESC";

	public Customer getCustomer(int cid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Customer customer = new Customer();
		try {
			pstmt = conn.prepareStatement(GET_CUSTOMER_BY_ID);
			pstmt.setInt(1, cid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				customer.setCid(rs.getInt("cid"));
				customer.setName(rs.getString("name"));
				customer.setAddress(rs.getString("address"));
				customer.setBank(rs.getString("bank"));
				customer.setAccount(rs.getString("account"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return customer;
	}

	public Customer getCustomerNew() {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Customer customer = new Customer();
		try {
			pstmt = conn.prepareStatement(GET_CUSTOMER_NEW);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				customer.setCid(rs.getInt("cid"));
				customer.setName(rs.getString("name"));
				customer.setAddress(rs.getString("address"));
				customer.setBank(rs.getString("bank"));
				customer.setAccount(rs.getString("account"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return customer;
	}

	public boolean addCustomer(Customer ct) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_CUSTOMER);

			pstmt.setString(1, ct.getName());
			pstmt.setString(2, ct.getAddress());
			pstmt.setString(3, ct.getBank());
			pstmt.setString(4, ct.getAccount());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean updateCustomer(Customer ct, int cid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_CUSTOMER);

			pstmt.setString(1, ct.getName());
			pstmt.setString(2, ct.getAddress());
			pstmt.setString(3, ct.getBank());
			pstmt.setString(4, ct.getAccount());
			pstmt.setInt(5, cid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean delCustomer(int cid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_CUSTOMER);

			pstmt.setInt(1, cid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getCustomers() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList customers = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_CUSTOMERS, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Customer customer = new Customer();

				customer.setCid(rs.getInt("cid"));
				customer.setName(rs.getString("name"));
				customer.setAddress(rs.getString("address"));
				customer.setBank(rs.getString("bank"));
				customer.setAccount(rs.getString("account"));

				customers.add(customer);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return customers;
	}

}
